﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Collections;


namespace Jurassic_Island
{
    public class CADCaminos
    {
        static private String cadenaConexion = ConfigurationManager.ConnectionStrings["JurassicBDConnectionString"].ConnectionString;
        static private String nombreTabla = "Caminos";

        public DataSet ObtenerTablaCaminos()
        {
            DataSet dsCaminos = new DataSet();

            try
            {
                SqlConnection conexion = new SqlConnection(cadenaConexion);
                String consulta = "Select id_cam as 'ID', origen_cam as 'Origen', destino_cam as 'Destino', coste_cam as 'Coste' from Caminos";
                SqlDataAdapter daCaminos = new SqlDataAdapter(consulta, conexion);
                daCaminos.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                daCaminos.Fill(dsCaminos, nombreTabla);
            }
            catch (Exception ex)
            {
                throw (ex);
            }

            return dsCaminos;
        }

        public DataSet ObtenerTablaCaminosPorID(int id_cam)
        {
            DataSet dsCaminos = new DataSet();

            try
            {
                SqlConnection conexion = new SqlConnection(cadenaConexion);
                String consulta = "Select id_cam as 'ID', origen_cam as 'Origen', destino_cam as 'Destino', coste_cam as 'Coste' from Caminos where id_cam="+id_cam;
                SqlDataAdapter daCaminos = new SqlDataAdapter(consulta, conexion);
                daCaminos.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                daCaminos.Fill(dsCaminos, nombreTabla);
            }
            catch (Exception ex)
            {
                throw (ex);
            }

            return dsCaminos;
        }

        public DataSet ObtenerTablaCaminosPorOrigen(String origen_cam)
        {
            DataSet dsCaminos = new DataSet();

            try
            {
                SqlConnection conexion = new SqlConnection(cadenaConexion);
                String consulta = "Select id_cam as 'ID', origen_cam as 'Origen', destino_cam as 'Destino', coste_cam as 'Coste' from Caminos where origen_cam='" + origen_cam + "'";
                SqlDataAdapter daCaminos = new SqlDataAdapter(consulta, conexion);
                daCaminos.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                daCaminos.Fill(dsCaminos, nombreTabla);
            }
            catch (Exception ex)
            {
                throw (ex);
            }

            return dsCaminos;
        }

        public DataSet ObtenerTablaCaminosPorDestino(String destino_cam)
        {
            DataSet dsCaminos = new DataSet();

            try
            {
                SqlConnection conexion = new SqlConnection(cadenaConexion);
                String consulta = "Select id_cam as 'ID', origen_cam as 'Origen', destino_cam as 'Destino', coste_cam as 'Coste' from Caminos where destino_cam='" + destino_cam + "'";
                SqlDataAdapter daCaminos = new SqlDataAdapter(consulta, conexion);
                daCaminos.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                daCaminos.Fill(dsCaminos, nombreTabla);
            }
            catch (Exception ex)
            {
                throw (ex);
            }

            return dsCaminos;
        }

        public DataSet ObtenerTablaCaminosPorOrigenDestino(String origen_cam, String destino_cam)
        {
            DataSet dsCaminos = new DataSet();

            try
            {
                SqlConnection conexion = new SqlConnection(cadenaConexion);
                String consulta = "Select id_cam as 'ID', origen_cam as 'Origen', destino_cam as 'Destino', coste_cam as 'Coste' from Caminos where origen_cam='" + origen_cam + "' and destino_cam='" + destino_cam + "'";
                SqlDataAdapter daCaminos = new SqlDataAdapter(consulta, conexion);
                daCaminos.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                daCaminos.Fill(dsCaminos, nombreTabla);
            }
            catch (Exception ex)
            {
                throw (ex);
            }

            return dsCaminos;
        }

        public DataSet ObtenerTablaCaminosPorLugar(String lugar)
        {
            DataSet dsCaminos = new DataSet();

            try
            {
                SqlConnection conexion = new SqlConnection(cadenaConexion);
                String consulta = "Select id_cam as 'ID', origen_cam as 'Origen', destino_cam as 'Destino', coste_cam as 'Coste' from Caminos where origen_cam like '%" + lugar + "%' or destino_cam like '%" + lugar + "%'";
                SqlDataAdapter daCaminos = new SqlDataAdapter(consulta, conexion);
                daCaminos.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                daCaminos.Fill(dsCaminos, nombreTabla);
            }
            catch (Exception ex)
            {
                throw (ex);
            }

            return dsCaminos;
        }



        public bool ActualizarCaminos(DataSet dsCaminos)
        {
            bool resultado = false;

            try
            {
                SqlConnection conexion = new SqlConnection(cadenaConexion);
                String consulta = "Select id_cam as 'ID', origen_cam as 'Origen', destino_cam as 'Destino', coste_cam as 'Coste' from Caminos";
                SqlDataAdapter daCamino = new SqlDataAdapter(consulta, conexion);
                SqlCommandBuilder cb = new SqlCommandBuilder(daCamino);
                cb.ConflictOption = ConflictOption.OverwriteChanges;
                daCamino.UpdateCommand = cb.GetUpdateCommand();
                daCamino.Update(dsCaminos,"Caminos");
                dsCaminos.Tables["Caminos"].AcceptChanges();

                resultado = true;
            }
            catch (Exception ex)
            {
                throw (ex);
            }

            return resultado;
        }
        
    }
}
